package com.dsmbase.corp.project.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;

import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Component;
import java.util.List;

import com.dsmbase.corp.common.JdbcTemplateAdapter;
import com.dsmbase.corp.project.domain.ProjectFlow;

@Component
public class ProjectFlowDao {

	private JdbcTemplate jdbcTemplate;
	
	private static final String SELECT_BY_ID_SQL = "select id, flow_id, node_id, originator_id, name, project_id, version, start_time, end_time, comment from project_flow where id = ?";
	private static final String SELECT_ALL_SQL = "select * from project_flow";
	private static final String INSERT_NEW_PROJECT = "INSERT INTO project_flow (flow_id, node_id, originator_id, name, project_id, version, start_time, end_time, comment) values(?, ?, ?, ?, ?, ?, ? ,? ,?)";
	private static final String UPDATE_SQL = "update project_flow set flow_id = ?, node_id = ? , originator_id = ?, name = ?, project_id = ?, version = ?, start_time = ?, end_time = ?, comment=? where id = ?";
	
	@Autowired
	private DataSource dataSource;
	
	@Autowired
	public void setDataSource(DataSource ds) {
		dataSource = ds;
		this.jdbcTemplate = new JdbcTemplateAdapter(dataSource);
	}
	
	public ProjectFlow getProjectById(int id) throws SQLException {
		Object[] params = new Object[] { id };
		ProjectFlow projectFlow = null;
		try {
			projectFlow = (ProjectFlow) jdbcTemplate.queryForObject(SELECT_BY_ID_SQL,
					params, new ProjectFlowRowMapper());
		} catch (EmptyResultDataAccessException e) {
			throw new SQLException(e);
		}
		return projectFlow;
	}
	
	public List<ProjectFlow> getAllProjects() throws SQLException {
		List<ProjectFlow> list = null;
		list = jdbcTemplate.query(SELECT_ALL_SQL, new ProjectFlowRowMapper());
		return list;
	}
	
	public int createProject(ProjectFlow project) throws SQLException {
		Object[] params = new Object[] { project.getFlowId(), project.getCurrentNodeId(), project.getOriginatorId(), project.getName(), project.getProjectId(), project.getVersion(), project.getStartTime(), project.getEndTime(), project.getComment() };
		int id = ((JdbcTemplateAdapter) jdbcTemplate).insertAndGetKey(INSERT_NEW_PROJECT, params);
		return id;
	}
	
	public class ProjectFlowRowMapper implements RowMapper<ProjectFlow> {

		public ProjectFlow mapRow(ResultSet rs, int index) throws SQLException {
			ProjectFlow projectFlow = new ProjectFlow();
			projectFlow.setId(rs.getInt("id"));
			projectFlow.setProjectId(rs.getString("project_id"));
			projectFlow.setFlowId(rs.getInt("flow_id"));
			projectFlow.setCurrentNodeId(rs.getInt("node_id"));
			projectFlow.setOriginatorId(rs.getInt("originator_id"));
			projectFlow.setStartTime(rs.getTimestamp("start_time"));
			projectFlow.setEndTime(rs.getTimestamp("end_time"));
			projectFlow.setVersion(rs.getString("version"));
			projectFlow.setComment(rs.getString("comment"));
			projectFlow.setName(rs.getString("name"));
			return projectFlow;
		}

	}
	
	public void updateProjectFlow(ProjectFlow project) throws SQLException {
		Object[] params = new Object[] { project.getFlowId(), project.getCurrentNodeId(), project.getOriginatorId(), 
				project.getName(), project.getProjectId(), project.getVersion(), project.getStartTime(), project.getEndTime(), project.getComment(),  project.getId() }; 
		int[] types = new int[] { Types.INTEGER, Types.INTEGER, Types.INTEGER, Types.VARCHAR, Types.VARCHAR, Types.VARCHAR, Types.TIMESTAMP, Types.TIMESTAMP, Types.VARCHAR, Types.INTEGER }; 
		jdbcTemplate.update(UPDATE_SQL, params, types); 
	}

	
}
